package org.goldenport.g3.messages.sql

import org.goldenport.g3.{G3Message, G3Command}
import org.goldenport.g3.messages._
import org.goldenport.g3.messages.query.RecordQuery
import SqlUtil.{symbol2name => to_name, value2literal => to_literal, makeExampleWhere}

/**
 * @since   Apr. 12, 2010
 * @version Jun. 26, 2011
 * @author  ASAMI, Tomoharu
 */
/**
 * Execute a sql clause.
 */
trait SqlMessage extends G3Message

abstract class SqlCommand(uris: List[String]) extends G3Command(uris) with SqlMessage {

  def this(uri: String) = this(List(uri))

  protected final def where_ids(idname: Symbol, ids: List[Any]) = {
    ids.map(to_name(idname) + "=" + to_literal(_)).mkString(" OR ")
  }
}

object Create {
  def apply(tableName: String, ddl: DDL) = new SqlCreate(tableName, ddl)
}

object Drop {
  def apply(tableName: String) = new SqlDrop(tableName)
}

object Select {
  def apply(tableName: String, idName: Symbol, ids: List[Any]) = {
    new SqlSelectId(tableName, idName, ids)
  }

  def apply(query: RecordQuery) = {
    new SqlSelectQuery(query)
  }

  def apply(tableName: String, example: Record) = {
    new SqlSelectExample(tableName, example)
  }

  def apply(tableName: String, expr: Expression) = new SqlSelectExpression(tableName, expr)

  def apply(statement: String) = new SqlSelectStatement(statement)
}

object Insert {
  def apply(tableName: String, record: Record) = {
    new SqlInsert(tableName, RecordSet(record))
  }

  def apply(tableName: String, records: RecordSet) = {
    new SqlInsert(tableName, records)
  }
}

object Update {
  def apply(tableName: String, record: Record) = {
    new SqlUpdateFetch(tableName, RecordSet(record))
  }

  def apply(tableName: String, records: RecordSet) = {
    new SqlUpdateFetch(tableName, records)
  }

  def apply(tableName: String, example: Record, data: Record) = {
    new SqlUpdateExample(tableName, example, data)
  }

  def apply(tableName: String, query: RecordQuery, data: Record) = {
    new SqlUpdateQuery(tableName, query, data)
  }
}

object Delete {
  def apply(tableName: String, idName: Symbol, id: Any) = {
    new SqlDeleteId(tableName, idName, List(id))
  }

  def apply(tableName: String, idName: Symbol, ids: List[Any]) = {
    new SqlDeleteId(tableName, idName, ids)
  }

  def apply(tableName: String, record: Record) = new SqlDeleteExample(tableName, record)

  def apply(tableName: String, expression: Expression) = new SqlDeleteExpression(tableName, expression)

  def apply(statement: String) = new SqlDeleteStatement(statement)
}

case class Sql(clause: String)

class SqlCreate(val tableName: String, val ddl: DDL) extends SqlCommand(tableName)

abstract class SqlSelect(uris: List[String]) extends SqlCommand(uris) {
  protected def this() = this(Nil)
  protected def this(uri: String) = this(List(uri))

  def statement: String
}

class SqlSelectId(val tableName: String, val idName: Symbol, val ids: List[Any]) extends SqlSelect(tableName) {
  def statement = {
    if (ids.isEmpty) {
      "SELECT * FROM %s".format(
        tableName)
    } else {
      "SELECT * FROM %s WHERE %s".format(
        tableName,
        where_ids(idName, ids))
    }
  }
}

class SqlSelectQuery(val query: RecordQuery) extends SqlSelect() {
  def statement = query.sqlStatement
}

class SqlSelectExample(val tableName: String, val example: Record) extends SqlSelect() {
  // TODO handle query by example speciall command
  def where = SqlUtil.makeExampleWhere(example)
  def statement = {
    if (example.isEmpty) {
      "SELECT * FROM %s".format(tableName)
    } else {
      "SELECT * FROM %s WHERE %s".format(tableName, where)
    }
  }
}

class SqlSelectExpression(val tableName: String, val expression: Expression) extends SqlSelect(tableName) {
  def statement = {
    val where = expression.sqlLiteral
    if (where == "") {
      "SELECT * FROM %s".format(tableName)
    } else {
      "SELECT * FROM %s WHERE %s".format(tableName, where)
    }
  }
}

class SqlSelectStatement(val statement: String) extends SqlSelect() {
}

class SqlInsert(val tableName: String, val records: RecordSet) extends SqlCommand(tableName)

abstract class SqlUpdate(val tableName: String) extends SqlCommand(tableName) {
  def statements: Seq[String]
}

class SqlUpdateFetch(tableName: String, val records: RecordSet) extends SqlUpdate(tableName) {
  private def _make_statement(record: Record) = {
    "UPDATE %s SET %s WHERE %s".format(
        tableName,
        record.sqlSetClause,
        record.sqlWhereClause
    )
  }

  def statements: Seq[String] = {
    records.map(_make_statement)
  }
}

class SqlUpdateExample(tableName: String, val example: Record, val data: Record) extends SqlUpdate(tableName) {
  private def _make_statement = {
    "UPDATE %s SET %s WHERE %s".format(
        tableName,
        data.sqlSetClause,
//        example.sqlWhereExampleClause
        SqlUtil.makeExampleWhere(example)
    )
  }
  
  override def statements: Seq[String] = {
    List(_make_statement)
  }
}

class SqlUpdateQuery(tableName: String, val query: RecordQuery, val data: Record) extends SqlUpdate(tableName) {
  private def _make_statement = {
    "UPDATE %s SET %s WHERE %s".format(
        tableName,
        data.sqlSetClause,
        query.whereClause
    )
  }
  
  override def statements: Seq[String] = {
    List(_make_statement)
  }
}

abstract class SqlDelete(uris: List[String]) extends SqlCommand(uris) {
  protected def this() = this(Nil)
  protected def this(uri: String) = this(List(uri))

  def statement: String
}

class SqlDeleteId(val tableName: String, val idName: Symbol, val ids: List[Any]) extends SqlDelete(tableName) {
  def statement = "DELETE FROM %s WHERE %s".format(
    tableName,
    where_ids(idName, ids)
  )
}

class SqlDeleteExample(val tableName: String, val example: Record) extends SqlDelete(tableName) {
  def statement = "DELETE FROM %s WHERE %s".format(
    tableName,
//        example.sqlWhereExampleClause
        SqlUtil.makeExampleWhere(example)
  )
}

class SqlDeleteExpression(val tableName: String, val expression: Expression) extends SqlDelete(tableName) {
  def statement = "DELETE FROM %s WHERE %s".format(
    tableName,
    expression.sqlLiteral
  )
}

class SqlDeleteStatement(val statement: String) extends SqlDelete()

class SqlDrop(val tableName: String) extends SqlCommand(tableName)

case class DDL(columns: Column*)

class Column(
  val name: Symbol,
  val datatype: SqlDatatype,
  val constraints: List[SqlConstraint])

object Column {
  def apply(name: Symbol, datatype: SqlDatatype): Column = {
    apply(name, datatype, Nil)
  }

  def apply(name: Symbol, datatype: SqlDatatype, constraints: List[SqlConstraint]) = {
    new Column(name, datatype, constraints)
  }
}

// SQL Datatype
abstract class SqlDatatype() {
  def fullName: String
}

abstract class SqlConstraint() {
  def fullName: String
}

class Array extends SqlDatatype {
  def fullName = "Array"
}

object Array extends Array {
  def apply() = new Array
}

class BIGINT extends SqlDatatype {
  def fullName = "BIGINT"
}

object BIGINT extends BIGINT {
  def apply() = new BIGINT
}

class BINARY extends SqlDatatype {
  def fullName = "BINARY"
}

object BINARY extends BINARY {
  def apply() = new BINARY
}

class BIT extends SqlDatatype {
  def fullName = "BIT"
}

object BIT extends BIT {
  def apply() = new BIT
}

class BLOB extends SqlDatatype {
  def fullName = "BLOB"
}

object BLOB extends BLOB {
  def apply() = new BLOB
}

class BOOLEAN extends SqlDatatype {
  def fullName = "BOOLEAN"
}

object BOOLEAN extends BOOLEAN {
  def apply() = new BOOLEAN
}

class CHAR(val length: Int) extends SqlDatatype {
  def fullName = "VARCHAR(%s)".format(length)
}

object CHAR {
  def apply(length: Int) = new CHAR(length)
}

class CLOB extends SqlDatatype {
  def fullName = "CLOB"
}

object CLOB extends CLOB {
  def apply() = new CLOB
}

class DATALINK extends SqlDatatype {
  def fullName = "DATALINK"
}

object DATALINK extends DATALINK {
  def apply() = new DATALINK
}

class DATE extends SqlDatatype {
  def fullName = "DATE"
}

object DATE extends DATE {
  def apply() = new DATE
}

class DECIMAL extends SqlDatatype {
  def fullName = "DECIMAL"
}

object DECIMAL extends DECIMAL {
  def apply() = new DECIMAL
}

class DISTINCT extends SqlDatatype {
  def fullName = "DISTINCT"
}

object DISTINCT extends DISTINCT {
  def apply() = new DISTINCT
}

class DOUBLE extends SqlDatatype {
  def fullName = "DOUBLE"
}

object DOUBLE extends DOUBLE {
  def apply() = new DOUBLE
}

class FLOAT extends SqlDatatype {
  def fullName = "FLOAT"
}

object FLOAT extends FLOAT {
  def apply() = new FLOAT
}

class INTEGER extends SqlDatatype {
  def fullName = "INTEGER"
}

object INTEGER extends INTEGER {
  def apply() = new INTEGER
}

class JAVA_OBJECT extends SqlDatatype {
  def fullName = "JAVA_OBJECT"
}

object JAVA_OBJECT extends JAVA_OBJECT {
  def apply() = new JAVA_OBJECT
}

class LONGNVARCHAR(val length: Int) extends SqlDatatype {
  def fullName = "LONGNVARCHAR(%s)".format(length)
}

object LONGNVARCHAR {
  def apply(length: Int) = new LONGNVARCHAR(length)
}

class LONGVARBINARY(val length: Int) extends SqlDatatype {
  def fullName = "LONGVARBINARY(%s)".format(length)
}

object LONGVARBINARY {
  def apply(length: Int) = new LONGVARBINARY(length)
}

class LONGVARCHAR(val length: Int) extends SqlDatatype {
  def fullName = "LONGVARCHAR(%s)".format(length)
}

object LONGVARCHAR {
  def apply(length: Int) = new LONGVARCHAR(length)
}

class NCHAR(val length: Int) extends SqlDatatype {
  def fullName = "NCHAR(%s)".format(length)
}

object NCHAR {
  def apply(length: Int) = new NCHAR(length)
}

class NCLOB extends SqlDatatype {
  def fullName = "NCLOB"
}

object NCLOB extends NCLOB {
  def apply() = new NCLOB
}

class NULL extends SqlDatatype {
  def fullName = "NULL"
}

object NULL extends NULL {
  def apply() = new NULL
}

class NUMERIC extends SqlDatatype {
  def fullName = "NUMERIC"
}

object NUMERIC extends NUMERIC {
  def apply() = new NUMERIC
}

class NVARCHAR(val length: Int) extends SqlDatatype {
  def fullName = "NVARCHAR(%s)".format(length)
}

object NVARCHAR {
  def apply(length: Int) = new NVARCHAR(length)
}

class OTHER extends SqlDatatype {
  def fullName = "OTHER"
}

object OTHER extends OTHER {
  def apply() = new OTHER
}

class REAL extends SqlDatatype {
  def fullName = "REAL"
}

object REAL extends REAL {
  def apply() = new REAL
}

class REF extends SqlDatatype {
  def fullName = "REF"
}

object REF extends REF {
  def apply() = new REF
}

class ROWID extends SqlDatatype {
  def fullName = "ROWID"
}

object ROWID extends ROWID {
  def apply() = new ROWID
}

class SMALLINT extends SqlDatatype {
  def fullName = "SMALLINT"
}

object SMALLINT extends SMALLINT {
  def apply() = new SMALLINT
}

class SQLXML extends SqlDatatype {
  def fullName = "SQLXML"
}

object SQLXML extends SQLXML {
  def apply() = new SQLXML
}

class STRUCT extends SqlDatatype {
  def fullName = "STRUCT"
}

object STRUCT extends STRUCT {
  def apply() = new STRUCT
}

class TIME extends SqlDatatype {
  def fullName = "TIME"
}

object TIME extends TIME {
  def apply() = new TIME
}

class TIMESTAMP extends SqlDatatype {
  def fullName = "TIMESTAMP"
}

object TIMESTAMP extends TIMESTAMP {
  def apply() = new TIMESTAMP
}

class TINYINT extends SqlDatatype {
  def fullName = "TINYINT"
}

object TINYINT extends TINYINT {
  def apply() = new TINYINT
}

class VARBINARY extends SqlDatatype {
  def fullName = "VARBINARY"
}

object VARBINARY extends VARBINARY {
  def apply() = new VARBINARY
}

class VARCHAR(val length: Int) extends SqlDatatype {
  def fullName = "VARCHAR(%s)".format(length)
}

object VARCHAR extends INTEGER {
  def apply(length: Int) = new VARCHAR(length)
}

// SQL Constraint
class PRIMARY_KEY extends SqlConstraint {
  def fullName = "PRIMARY KEY"
}

object PRIMARY_KEY extends PRIMARY_KEY {
  def apply() = new PRIMARY_KEY
}

class AUTO_INCREMENT extends SqlConstraint {
  def fullName = "AUTO_INCREMENT"
}

object AUTO_INCREMENT extends AUTO_INCREMENT {
  def apply() = new AUTO_INCREMENT
}

class UNIQUE extends SqlConstraint {
  def fullName = "UNIQUE"
}

object UNIQUE extends AUTO_INCREMENT {
  def apply() = new UNIQUE
}
